package com.bifrii.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

import com.bifrii.transferObjects.CategoryCommunity;
import com.bifrii.transferObjects.Community;
import com.bifrii.transferObjects.Event;
import com.bifrii.transferObjects.User;


public class JDBCCommunityDAO implements ICommunityDAO {

	@Override
	public void insert(Integer idCommunity, Community c) {
		
		Connection conn = ConnectionManager.getInstance().checkOut();
		
        String sql = "INSERT INTO Community ( oid, idCommunity, idCreator, name, description, avatar, idCategory) VALUES (?, ?, ?, ?, ?, ?, ?) ";
        PreparedStatement stmt = null;

        try {
            stmt = conn.prepareStatement(sql);
            
            stmt.setString(1, c.getOid());
            stmt.setInt(2, idCommunity);
            stmt.setInt(3, c.getIdCreator());
            stmt.setString(4, c.getName());
            stmt.setString(5, c.getDescription());
            stmt.setString(6, c.getAvatar());
            stmt.setInt(7, c.getIdCategory());

            stmt.executeUpdate();

        } catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }

    }


	

	@Override
	public List<Community> selectAllCommunities() {

		Connection conn = ConnectionManager.getInstance().checkOut();

        PreparedStatement stmt = null;
        List<Community> searchResults = new LinkedList<Community>();
        ResultSet result = null;

        try {

            String sql = "SELECT * FROM Community order by idCommunity desc";
            stmt = conn.prepareStatement(sql); //conn es una conexi�n v�lida
            stmt.executeQuery();
            result = stmt.executeQuery();

            while (result.next()) {
                Community temp = new Community();
                temp.setOid(result.getString("oid"));
                temp.setIdCommunity(result.getInt("idCommunity"));
                temp.setIdCategory(result.getInt("idCategory"));
                temp.setIdCreator(result.getInt("idCreator"));
                temp.setDescription(result.getString("description"));
                temp.setAvatar(result.getString("avatar"));
                temp.setName(result.getString("name"));
                searchResults.add(temp);
            }
        } catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            ConnectionManager.getInstance().checkIn(conn);
            try {
                if (result != null)
                    result.close();
                if (stmt != null)
                    stmt.close();
            } catch (SQLException e) {
            }

        }
        return searchResults;

	}

	public List<Community> selectAllCommunitiesForUser(Integer idUser) {

		Connection conn = ConnectionManager.getInstance().checkOut();

        PreparedStatement stmt = null;
        List<Community> searchResults = new LinkedList<Community>();
        ResultSet result = null;

        try {

            String sql = "SELECT * FROM Community c, CommunityUserRole cur where cur.idUser = ? and cur.idCommunity = c.idCommunity order by c.idCommunity desc";
            stmt = conn.prepareStatement(sql); //conn es una conexi�n v�lida
            stmt.setInt(1, idUser);
            stmt.executeQuery();
            result = stmt.executeQuery();

            while (result.next()) {
                Community temp = new Community();
                temp.setOid(result.getString("oid"));
                temp.setIdCommunity(result.getInt("idCommunity"));
                temp.setIdCategory(result.getInt("idCategory"));
                temp.setIdCreator(result.getInt("idCreator"));
                temp.setDescription(result.getString("description"));
                temp.setAvatar(result.getString("avatar"));
                temp.setName(result.getString("name"));
                searchResults.add(temp);
            }
        } catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            ConnectionManager.getInstance().checkIn(conn);
            try {
                if (result != null)
                    result.close();
                if (stmt != null)
                    stmt.close();
            } catch (SQLException e) {
            }

        }
        return searchResults;

	}
	@Override
	public Community select(Integer idCommunity) {
		Connection conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
        ResultSet result = null;
        Community c = null;
        String sql = "SELECT * FROM Community WHERE (idCommunity = ?) ";

        try {
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, idCommunity);
            result = stmt.executeQuery();

            result.next();
            c = new Community();
            c.setOid(result.getString("oid"));
            c.setIdCommunity(result.getInt("idCommunity"));
            c.setIdCategory(result.getInt("idCategory"));
            c.setIdCreator(result.getInt("idCreator"));
            c.setDescription(result.getString("description"));
            c.setAvatar(result.getString("avatar"));
            c.setName(result.getString("name"));
            
        } catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
                if (result != null) {
                    result.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }
        return c;
	}

	@Override
	public void delete(Integer idCommunity) {
		Connection conn = ConnectionManager.getInstance().checkOut();
		
		String sql = "DELETE FROM Community WHERE (idCommunity = ?) ";
        PreparedStatement stmt = null;

        try {
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, idCommunity);
            stmt.executeUpdate();
        } catch (SQLException e) {
            System.err.println("Message: " + e.getMessage());
            System.err.println("SQLState: " + e.getSQLState());
            System.err.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
                if (stmt != null)
                    stmt.close();
            } catch (SQLException e) {
            }
        }
    }

	@Override
	public List<User> selectAllUsers(Integer idCommunity) {
		Connection conn = ConnectionManager.getInstance().checkOut();

        PreparedStatement stmt = null;
        List<User> searchResults = new LinkedList<User>();
        ResultSet result = null;

        try {

            String sql = "SELECT * FROM User u, CommunityUserRole cur where (cur.idCommunity = ? ) and cur.idUser = u.idUser";
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, idCommunity);
            stmt.executeQuery();
            result = stmt.executeQuery();

            while (result.next()) {
                User temp = new User();
                temp.setOid(result.getString("oid"));
                temp.setIdUser(result.getInt("idUser"));
                temp.setUser(result.getString("user"));
                temp.setPassword(result.getString("password"));
             
                searchResults.add(temp);
            }
            
        } catch (SQLException ex) {
            System.out.println("Message: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("ErrorCode: " + ex.getErrorCode());
        } finally {
            ConnectionManager.getInstance().checkIn(conn);
            try {
                if (result != null)
                    result.close();
                if (stmt != null)
                    stmt.close();
            } catch (SQLException ex) {
            }

        }
        return searchResults;
	}

	@Override
	public void insertUser(String oid,Integer idCommunity, Integer idUser, Integer idRole) {
		Connection conn = ConnectionManager.getInstance().checkOut();
		
        String sql = "INSERT INTO CommunityUserRole ( oid, idCommunity, idUser, idRole) VALUES (?, ?, ?, ?) ";
        PreparedStatement stmt = null;

        try {
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, oid);
            stmt.setInt(2, idCommunity);
            stmt.setInt(3, idUser);
            stmt.setInt(4, idRole);
      

            stmt.executeUpdate();

        } catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }

		
	}




	@Override
	public List<CategoryCommunity> selectCategories(Integer idCommunity) {
		Connection conn = ConnectionManager.getInstance().checkOut();
		
        PreparedStatement stmt = null;
        List<CategoryCommunity> searchResults = new LinkedList<CategoryCommunity>();
        ResultSet result = null;

        try {
        	
            String sql = "SELECT * FROM Community c, CategoryCommunity cc where (c.idCommunity = ? ) and c.idCategory = cc.idCategory";
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, idCommunity);
            stmt.executeQuery();
            result = stmt.executeQuery();

            while (result.next()) {
                CategoryCommunity temp = new CategoryCommunity();
                temp.setOid(result.getString("oid"));
                temp.setIdCategory(result.getInt("idCategory"));
                temp.setName(result.getString("name"));
             
                searchResults.add(temp);
            }
            
        } catch (SQLException ex) {
            System.out.println("Message: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("ErrorCode: " + ex.getErrorCode());
        } finally {
            ConnectionManager.getInstance().checkIn(conn);
            try {
                if (result != null)
                    result.close();
                if (stmt != null)
                    stmt.close();
            } catch (SQLException ex) {
            }

        }
        return searchResults;
	}
	public List<Event> selectEvents(Integer idCommunity){
		Connection conn = ConnectionManager.getInstance().checkOut();

        PreparedStatement stmt = null;
        List<Event> searchResults = new LinkedList<Event>();
        ResultSet result = null;

        try {

            String sql = "SELECT * FROM Event where (idCommunity = ? )";
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, idCommunity);
            stmt.executeQuery();
            result = stmt.executeQuery();

            while (result.next()) {
                Event temp = new Event();
                temp.setIdEvent(result.getInt("idEvent"));
                temp.setOid(result.getString("oid"));
                temp.setIdCreator(result.getInt("idCreator"));
                temp.setIdCommunity(idCommunity);
                temp.setLimitAssistance(result.getInt("limitAssistance"));
                temp.setName(result.getString("name"));
                temp.setDescription(result.getString("description"));
             
                searchResults.add(temp);
            }
        } catch (SQLException ex) {
            System.out.println("Message: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("ErrorCode: " + ex.getErrorCode());
        } finally {
            ConnectionManager.getInstance().checkIn(conn);
            try {
                if (result != null)
                    result.close();
                if (stmt != null)
                    stmt.close();
            } catch (SQLException ex) {
            }

        }
        return searchResults;}
	public List<Community> selectTopCommunities(Integer top) {

		Connection conn = ConnectionManager.getInstance().checkOut();

        PreparedStatement stmt = null;
        List<Community> searchResults = new LinkedList<Community>();
        ResultSet result = null;

        try {

            String sql = "SELECT distinct *, count(cur.idCommunity) as countCommunity FROM CommunityUserRole cur, Community c where cur.idCommunity = c.idCommunity group by cur.idCommunity order by countCommunity desc limit 0,?";
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, top);
            stmt.executeQuery();
            result = stmt.executeQuery();

            while (result.next()) {
                Community temp = new Community();
                temp.setOid(result.getString("oid"));
                temp.setIdCommunity(result.getInt("idCommunity"));
                temp.setIdCategory(result.getInt("idCategory"));
                temp.setIdCreator(result.getInt("idCreator"));
                temp.setDescription(result.getString("description"));
                temp.setAvatar(result.getString("avatar"));
                temp.setName(result.getString("name"));
                searchResults.add(temp);
            }
        } catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            ConnectionManager.getInstance().checkIn(conn);
            try {
                if (result != null)
                    result.close();
                if (stmt != null)
                    stmt.close();
            } catch (SQLException e) {
            }

        }
        return searchResults;

	}
	
}
